﻿<%@ WebHandler Language="C#" Class="GZWorkcenterTaskDashBoardV3" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 工装工具厂--工区看板v3.0
 * Copyright(c): 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2024.7.16
 */

public class GZWorkcenterTaskDashBoardV3 : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//主页面表格数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisMonthBarChart":
                        r = getThisMonthBarChart(jObject);//本月计划--柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisWeekBarChart":
                        jObject["selectdate"] = jObject["thisweek"].ToString();
                        r = getThisMonthBarChart(jObject);//本周计划--柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerPauseChart":
                        r = getContainerPauseChart(jObject);//工序的时间排布【批次停滞信息】
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getUnFinishWorktimeChart":
                        r = getUnFinishWorktimeChart(jObject);//工人未完成工时
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisMonthChartDetail"://本月计划--明细
                        r = getThisMonthChartDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisWeekChartDetail"://本周计划--明细
                        jObject["selectdate"] = jObject["thisweek"].ToString();
                        r = getThisMonthChartDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerPauseDetail":
                        r = getContainerPauseDetail(jObject);//工序的时间分布--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getUnFinishWorktimeDetail":
                        r = getUnFinishWorktimeDetail(jObject);//工人未完工工时--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChildContainer":
                        r = getChildContainer(jObject);//第一级子零件批次
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChildContainerSpec":
                        r = getChildContainerSpec(jObject);//第一级子零件批次各工序
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMissingPart":
                        r = getMissingPart(jObject);//查询缺件
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getNotFinishedChildContainer":
                        r = getNotFinishedChildContainer(jObject);//未完成子零件
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSkipStepInfo":
                        r = getSkipStepInfo(jObject);//查询跳序信息(根据顶层批次查)
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChildContainerSkipInfo":
                        r = getChildContainerSkipInfo(jObject);//查询子批次跳序信息（根据子批次号）
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getParamsStr(JObject jObject)
    {
        string strParams = "";
        //分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            strParams += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        //工区
        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "工装工具厂")
        {
            strParams += string.Format(" AND cci.fromworkcentername = '{0}'", jObject["workcentername"].ToString());
        }

        if (jObject["type"] != null)
        {
            string type = jObject["type"].ToString();
            if (type == "getThisWeekBarChart" || type == "getThisWeekChartDetail")
            {
                JArray weekplanfinishdate = JArray.Parse(jObject["weekplanfinishdate"].ToString());
                strParams += string.Format(" AND od.weekplanfinishdate >= TO_DATE('{0}','yyyy-mm-dd')", weekplanfinishdate[0].ToString());
                strParams += string.Format(" AND od.weekplanfinishdate <= TO_DATE('{0}','yyyy-mm-dd')", weekplanfinishdate[1].ToString());
            }
            else
            {
                JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
                strParams += string.Format(" AND od.enddate >= TO_DATE('{0}','yyyy-mm-dd')", selectdate[0].ToString());
                strParams += string.Format(" AND od.enddate <= TO_DATE('{0}','yyyy-mm-dd')", selectdate[1].ToString());
            }
        }

        //主制工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            strParams += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //订单类型
        if (jObject["ordertype"] != null && jObject["ordertype"].ToString() != "")
        {
            strParams += string.Format(" AND od.ordertype = '{0}'", jObject["ordertype"].ToString());
        }

        //计划类型
        if (jObject["plantype"] != null && jObject["plantype"].ToString() != "")
        {
            strParams += string.Format(" AND od.plantype = '{0}'", jObject["plantype"].ToString());
        }

        //是否超期
        if (jObject["isdelay"] != null && jObject["isdelay"].ToString() != "")
        {
            string isdelay = jObject["isdelay"].ToString();
            if (isdelay == "是")
            {
                strParams += " AND TRUNC(SYSDATE) > od.enddate";
            }
            else
            {
                strParams += " AND TRUNC(SYSDATE) <= od.enddate";
            }
        }

        //零件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            strParams += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //批次号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            strParams += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //重要程度
        if (jObject["priority"] != null && jObject["priority"].ToString() != "")
        {
            strParams += string.Format(" AND od.priority LIKE '%{0}%'", jObject["priority"].ToString());
        }

        //风险等级
        if (jObject["risklevel"] != null && jObject["risklevel"].ToString() != "")
        {
            strParams += string.Format(" AND od.risklevel LIKE '%{0}%'", jObject["risklevel"].ToString());
        }

        //是否入库
        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() != "总套数")
        {
            strParams += " AND cci.isinstore = 1";
        }

        if (jObject["name"] != null)
        {
            string name = jObject["name"].ToString();
            if (name == "品种月计划")
            {
                strParams += " AND od.ordertype = '品种'";
            }
            else
            {
                strParams += " AND od.ordertype = '临时'";
            }
        }

        return strParams;
    }

    //表格数据
    Result getData(JObject jObject, bool isPage)
    {
        int total = 0;
        DataTable dt = getDataCommonMethod(jObject, true, out total);
        Result r = new Result();
        r.data = dt;
        r.total = total;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    DataTable getDataCommonMethod(JObject jObject, bool isPage, out int total)
    {
        string strParams = getParamsStr(jObject);
        //1.有跳序的数据
        DataTable dtSkipData = getIsSkipData(strParams);
        //2.状态数据
        DataTable dtTopContainerStatus = getTopContainerStatus(strParams, jObject);
        //3.子零件完成数
        DataTable dtChildContainerFinishInfo = getFinishInfoData(strParams);
        //4.是否齐套及缺件信息
        DataTable dtLackInfo = getLackInfo(strParams, jObject);

        string skipFilter = "";
        if (jObject["isskip"] != null && jObject["isskip"].ToString() != "")
        {
            string isSkip = jObject["isskip"].ToString();
            //有跳序数据
            if (dtSkipData.Rows.Count > 0)
            {
                foreach (DataRow dr in dtSkipData.Rows)
                {
                    skipFilter += string.Format("'{0}',", dr["topcontainerid"].ToString());
                }
                if (isSkip == "是")
                {
                    skipFilter = string.Format(" AND cci.containerid IN ({0}) ", skipFilter.TrimEnd(','));
                }
                else
                {
                    skipFilter = string.Format(" AND cci.containerid NOT IN ({0}) ", skipFilter.TrimEnd(','));
                }
            }
            else //无跳序数据
            {
                if (isSkip == "是")
                {
                    skipFilter = " AND 1=0 ";
                }
                else
                {
                    skipFilter = " AND 1=1 ";
                }
            }
        }

        string statusFilter = "";
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (dtTopContainerStatus.Rows.Count > 0)
            {
                foreach (DataRow dr in dtTopContainerStatus.Rows)
                {
                    statusFilter += string.Format("'{0}',", dr["containerid"].ToString());
                }
                statusFilter = string.Format(" AND cci.containerid IN ({0}) ", statusFilter.TrimEnd(','));
            }
            else
            {
                statusFilter = " AND 1=0 ";
            }
        }


        string allSetFilter = "";
        if (jObject["isallset"] != null && jObject["isallset"].ToString() != "")
        {
            if (dtLackInfo.Rows.Count > 0)
            {
                foreach (DataRow dr in dtLackInfo.Rows)
                {
                    allSetFilter += string.Format("'{0}',", dr["containerid"].ToString());
                }
                allSetFilter = string.Format(" AND cci.containerid IN ({0}) ", allSetFilter.TrimEnd(','));
            }
            else
            {
                allSetFilter = " AND 1=0 ";
            }
        }

        string filter = string.Format("{0} {1} {2}", skipFilter, statusFilter, allSetFilter);
        string sql = getWorktimeFinishRateSql(strParams, jObject, filter);

        DataTable dt;
        uMESPagingDataDTO dto = new uMESPagingDataDTO();
        if (isPage)
        {
            dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
            dt = dto.DBTable;
        }
        else
        {
            dt = dal.GetDataTable(sql);
        }

        dt.Columns.Add("finishInfo");//子零件完成数
        dt.Columns.Add("isskip");//是否跳序
        dt.Columns.Add("isallset");//是否齐套
        dt.Columns.Add("lackqty");//缺件情况
        dt.Columns.Add("status");//状态

        foreach (DataRow dr in dt.Rows)
        {
            DataRow[] drs = dtSkipData.Select(string.Format("topcontainerid = '{0}'", dr["containerid"].ToString()));
            //追加：【是否跳序】
            if (drs.Length > 0)
            {
                dr["isskip"] = drs[0]["isskip"].ToString();
            }
            else
            {
                dr["isskip"] = "否";
            }

            //追加：【子零件完成数】
            drs = dtChildContainerFinishInfo.Select(string.Format("topcontainerid = '{0}'", dr["containerid"].ToString()));
            if (drs.Length > 0)
            {
                dr["finishInfo"] = drs[0]["finishInfo"].ToString();
            }
            else
            {
                dr["finishInfo"] = "";
            }

            //追加：【是否齐套】、【缺件情况】
            drs = dtLackInfo.Select(string.Format("containerid = '{0}'", dr["containerid"].ToString()));
            if (drs.Length > 0)
            {
                dr["isallset"] = drs[0]["isallset"].ToString();
                dr["lackqty"] = drs[0]["lackqty"].ToString();
            }
            else
            {
                dr["isallset"] = "";
                dr["lackqty"] = "";
            }

            //追加：【状态】
            drs = dtTopContainerStatus.Select(string.Format("containerid = '{0}'", dr["containerid"].ToString()));
            if (drs.Length > 0)
            {
                dr["status"] = drs[0]["status"].ToString();
            }
            else
            {
                dr["status"] = "";
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        if (isPage)
        {
            total = Convert.ToInt32(dto.RowCount);
        }
        else
        {
            total = dt.Rows.Count;
        }
        return dt;
    }
    
    //总图第一级子件号批次
    string getChildContainerSql(JObject jObject)
    {
        string sql = @"
SELECT t.topcontainername,t.topcontainerid,
       t.productname,t.startdate,t.enddate,t.ordertype,t.plantype,t.containername,t.containerid,t.workcentername,t.workflowstepname currentspec,
       t.isallset,t.delaystatus,t.isinstore,
       SUM(t.durationperunit) totalworktime,SUM(t.worktime) finishedworktime,
       CASE WHEN SUM(t.durationperunit) = 0 THEN 100
            ELSE ROUND(SUM(t.worktime)/SUM(t.durationperunit),2) * 100 
       END worktimerate,t.priority,t.risklevel,t.isskip,t1.status
FROM (
    SELECT cci.containername topcontainername,
           cci.containerid topcontainerid,
           cci2.productname,od.startdate,od.enddate,od.ordertype,od.plantype,
           cci2.containername,cci2.containerid,
           cci2.workcentername,cci2.workflowstepname,
           CASE WHEN pp.isallset = '否' THEN '否' ELSE '是' END isallset,
           CASE WHEN TRUNC(SYSDATE) >= od.enddate THEN TRUNC(SYSDATE) - od.enddate || '天' ELSE '否' END delaystatus,
           DECODE(cci2.isinstore,1,'是','否') isinstore,
           NVL(sd.durationperunit,0) durationperunit,
           CASE WHEN wt.containersteptimesheetid IS NOT NULL 
                THEN COALESCE(wts.splitduration,wt.totalduration,ROUND(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0)
                ELSE 0
           END worktime,
           od.priority,od.risklevel,NVL(n.isskip,'否') isskip
    FROM containercurrentinfo cci
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
    LEFT JOIN product p ON p.productid = cci.productid
    LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND cci.productrevision = bb.productrev
    LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
    LEFT JOIN gzpreparemanagement pp ON pp.containername = cci2.containername--子零件批次生产准备信息
    LEFT JOIN factory f ON f.factoryid = cci2.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
    LEFT JOIN containersteptimesheet wt ON wt.containerid = cci2.containerid AND wt.workflowstepid = ws.workflowstepid
    LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
    LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = wt.containerstepworkreportid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    LEFT JOIN (
         SELECT s.containerid,s.isinstore,CASE WHEN SUM(DECODE(s.checkdate,NULL,1,0)) > 0 THEN '是' ELSE '否' END isskip
         FROM
         (
            SELECT cci.containerid topcontainerid,cci.containername topcontainername,
                   cci2.containerid,cci2.containername,cci2.isinstore,ws.workflowstepname,ws.workflowstepid,MAX(csd.checkdate) checkdate
            FROM containercurrentinfo cci
            INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
            LEFT JOIN product p ON p.productid = cci.productid
            LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
            LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
            LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
            LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
            LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
            LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
            LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
            WHERE s.opdesc <> '虚拟序' AND cm.containercommidetailid IS NULL AND cci.containerid = '{0}' AND cci2.isinstore = 1
            GROUP BY cci.containerid,cci.containername,cci2.containerid,cci2.containername,cci2.isinstore,ws.workflowstepname,ws.workflowstepid
          ) s
          GROUP BY s.containerid,s.isinstore
    ) n ON n.containerid = cci2.containerid
    WHERE s.opdesc <> '虚拟序' AND cm.containercommidetailid IS NULL AND NVL(csw.checktype,0) <> 1 AND cci.containerid = '{0}'
) t
LEFT JOIN 
(
    SELECT cci.containerid topcontainerid,cci.containername topcontainername,
           cci2.containerid,cci2.containername,cci2.isinstore,cci2.workflowstepname,
           CASE WHEN cci2.isinstore = 1 THEN '完工入库'
                WHEN cm.containercommidetailid IS NOT NULL THEN '已外委'
                WHEN MAX(csd.checkdate) IS NULL AND MAX(csd.reportdate) IS NOT NULL THEN '待检验'
                WHEN MAX(csd.reportdate) IS NULL AND MAX(csd.dispatchdate) IS NOT NULL THEN '待报工'
                WHEN MAX(csd.dispatchdate) IS NULL THEN '待派工'
           END status
    FROM containercurrentinfo cci
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
    LEFT JOIN product p ON p.productid = cci.productid
    LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
    LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
    LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = cci2.workflowstepid
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = cci2.workflowstepid AND cm.isuse = 0
    WHERE cci.containerid = '{0}'
    GROUP BY cci.containerid,cci.containername,cci2.containerid,cci2.containername,cci2.isinstore,cci2.workflowstepname,cm.containercommidetailid
) t1 ON t1.containerid = t.containerid
GROUP BY t.topcontainername,t.topcontainerid,t.productname,t.startdate,t.enddate,t.ordertype,t.plantype,
         t.containername,t.containerid,t.workcentername,t.workflowstepname,t.isallset,t.delaystatus,t.isinstore,t.priority,t.risklevel,t.isskip,t1.status
ORDER BY t.containername";
        string containerid = jObject["containerid"].ToString();
        sql = string.Format(sql, containerid);
        return sql;
    }

    // 本月计划
    Result getThisMonthBarChart(JObject jObject)
    {
        int total = 0;
        DataTable dt = getDataCommonMethod(jObject, false, out total);
        DataTable dtResult = new DataTable();

        dtResult.Columns.Add("pztotal");//品种计划数
        dtResult.Columns.Add("pzfinished");//品种已完成
        dtResult.Columns.Add("pzfinishrate");//品种完成率
        dtResult.Columns.Add("lstotal");//临时计划数
        dtResult.Columns.Add("lsfinished");//临时完成数
        dtResult.Columns.Add("lsfinishrate");//临时完成率

        DataRow dr = dtResult.NewRow();

        int pztotal = dt.Select("ordertype = '品种'").Length;
        int pzfinished = dt.Select("ordertype = '品种' AND isinstore = '是'").Length;
        int lstotal = dt.Select("ordertype = '临时'").Length;
        int lsfinished = dt.Select("ordertype = '临时' AND isinstore = '是'").Length;

        dr["pztotal"] = pztotal;
        dr["pzfinished"] = pzfinished;
        dr["lstotal"] = lstotal;
        dr["lsfinished"] = lsfinished;

        if (pztotal == 0)
        {
            dr["pzfinishrate"] = 0;
        }
        else
        {
            dr["pzfinishrate"] = Math.Round((double)pzfinished / pztotal, 2);
        }

        if (lstotal == 0)
        {
            dr["lsfinishrate"] = 0;
        }
        else
        {
            dr["lsfinishrate"] = Math.Round((double)lsfinished / lstotal, 2);
        }

        dtResult.Rows.Add(dr);
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工序的时间排布【批次停滞信息】公用sql
    string getContainerPauseInfoSql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,cci.mfgordername,NVL(cci.familylistnames,'空') familylistnames,cci.productname,cci.productdescription,
        cci.containername,cci.qty,cci.workflowstepname,cci.fromworkcentername,
        e.fullname,c.originalstartdate,c.lastactivitydate,TRUNC(SYSDATE) - TRUNC(c.lastactivitydate) remaindays
FROM containercurrentinfo cci
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN employee e ON e.employeeid = c.startemployeeid
LEFT JOIN mes_factory_mapping f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cci.mfgorderid
LEFT JOIN Orderstatus od ON od.orderstatusid = mfg.orderstatusid
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN (
    SELECT cuc.subcontainerid containerid,MIN(csd.toacceptdate) toacceptdate
    FROM containersubcontainer cuc
    INNER JOIN containercurrentinfo cci ON cci.containerid = cuc.subcontainerid
    INNER JOIN containershiprecord csd ON csd.containerid = cuc.containerid
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cci.mfgorderid
    LEFT JOIN Orderstatus od ON od.orderstatusid = mfg.orderstatusid
    WHERE cci.isinstore = 0 AND cci.containerstatus <> 2 AND NVL(od.orderstatusname,'打开') <> '关闭' AND csd.toacceptdate IS NOT NULL
    GROUP BY cuc.subcontainerid
) csd ON csd.containerid = cci.containerid
WHERE cci.isinstore = 0 AND cci.containerstatus = 1 AND NVL(od.orderstatusname,'打开') <> '关闭' 
    AND f.factoryid = '{0}'
    AND (cci.workflowroute IS NULL OR (cci.workflowroute IS NOT NULL AND csd.toacceptdate IS NOT NULL))
    AND cci.fromworkcenterid = '{1}'";
        if (jObject["interval"] != null)
        {
            string interval = jObject["interval"].ToString();
            if (interval == "0-7天")
            {
                sql += " AND TRUNC(SYSDATE) - TRUNC(c.lastactivitydate) >= 0";
                sql += " AND TRUNC(SYSDATE) - TRUNC(c.lastactivitydate) < 7";
            }
            else if (interval == "7-30天")
            {
                sql += " AND TRUNC(SYSDATE) - TRUNC(c.lastactivitydate) >= 7";
                sql += " AND TRUNC(SYSDATE) - TRUNC(c.lastactivitydate) < 30";
            }
            else if (interval == "30-60天")
            {
                sql += " AND TRUNC(SYSDATE) - TRUNC(c.lastactivitydate) >= 30";
                sql += " AND TRUNC(SYSDATE) - TRUNC(c.lastactivitydate) < 60";
            }
            else
            {
                sql += " AND TRUNC(SYSDATE) - TRUNC(c.lastactivitydate) >= 60";//60天以上
            }
        }
        sql = string.Format(sql, jObject["factoryid"].ToString(), jObject["workcenterid"].ToString());
        return sql;
    }

    //工序的时间排布【批次停滞信息】
    Result getContainerPauseChart(JObject jObject)
    {
        string sql = @"
SELECT SUM(CASE WHEN t.remaindays >= 0 AND t.remaindays < 7 THEN 1 ELSE 0 END) zero_to_seven,
       SUM(CASE WHEN t.remaindays >= 7 AND t.remaindays < 30 THEN 1 ELSE 0 END) seven_to_thirty,
       SUM(CASE WHEN t.remaindays >= 30 AND t.remaindays < 60 THEN 1 ELSE 0 END) thirty_to_sixty,
       SUM(CASE WHEN t.remaindays >= 60 THEN 1 ELSE 0 END) more_than_sixty
FROM ({0}) t ";
        sql = string.Format(sql, getContainerPauseInfoSql(jObject));
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    // 工人未完成工时 公用sql
    string getUnFinishWorktimeSql(JObject jObject)
    {
        string sql = @"
SELECT csd.productname,csd.containername,csd.workflowstepname,sd.durationperunit,csd.workcentername,cci.fromworkcentername,
       csd.dispatchemployeename,csd.dispatchdate,csd.reportemployeename,csd.reportdate,
       NVL(e2.fullname,e.fullname) dispatchto,
       CASE WHEN csd.reportdate IS NOT NULL THEN '待检验'
            WHEN csd.reportdate IS NULL THEN '待报工'
       END status,
       CASE WHEN csd.workcentername <> cci.fromworkcentername THEN 1 ELSE 0 END iscoop
FROM containersteprecord csd
LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
LEFT JOIN factory f ON f.factoryid = csd.factoryid
LEFT JOIN containerstepdispatch d ON d.containerstepdispatchid = csd.dispatchid
LEFT JOIN containerstepdispatchdetail dd ON dd.containerstepdispatchid = d.containerstepdispatchid
LEFT JOIN employee e ON e.employeeid = d.workerid
LEFT JOIN employee e2 ON e2.employeeid = dd.workerid
LEFT JOIN stepschedulingdetail sd ON sd.stepid = csd.workflowstepid
WHERE csd.factoryid = '{0}' AND csd.workcenterid = '{1}' AND cci.fromworkcenterid = '{1}'
      AND csd.dispatchdate >= to_date('{2} 00:00:00','yyyy/mm/dd hh24:mi:ss')
      AND csd.dispatchdate <= to_date('{3} 23:59:59','yyyy/mm/dd hh24:mi:ss')
      AND csd.usecompleted = 0";

        if (jObject["worker"] != null)
        {
            sql += string.Format(" AND NVL(e2.fullname,e.fullname) = '{0}'", jObject["worker"].ToString());
        }

        sql += @"
UNION ALL
SELECT cci.productname,cci.containername,ws.workflowstepname,sd.durationperunit,csd.workcentername,cci.fromworkcentername,
       csd.dispatchemployeename,csd.dispatchdate,csd.reportemployeename,csd.reportdate,
       NVL(e2.fullname,e.fullname) dispatchto,
       CASE WHEN csd.reportdate IS NOT NULL THEN '待检验'
            WHEN csd.reportdate IS NULL THEN '待报工'
       END status,
       CASE WHEN csd.workcentername <> cci.fromworkcentername THEN 1 ELSE 0 END iscoop
FROM containerworkcenterdispatch wd
LEFT JOIN containercurrentinfo cci ON cci.containerid = wd.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = wd.workflowstepid
LEFT JOIN stepschedulingdetail sd ON sd.stepid = wd.workflowstepid
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = wd.workflowstepid
LEFT JOIN containerstepdispatch d ON d.containerstepdispatchid = csd.dispatchid
LEFT JOIN containerstepdispatchdetail dd ON dd.containerstepdispatchid = d.containerstepdispatchid
LEFT JOIN employee e ON e.employeeid = d.workerid
LEFT JOIN employee e2 ON e2.employeeid = dd.workerid
WHERE csd.factoryid = '{0}' AND wd.toworkcenterid = '{1}'
      AND csd.dispatchdate >= to_date('{2} 00:00:00','yyyy/mm/dd hh24:mi:ss')
      AND csd.dispatchdate <= to_date('{3} 23:59:59','yyyy/mm/dd hh24:mi:ss')
      AND csd.usecompleted = 0";

        if (jObject["worker"] != null)
        {
            sql += string.Format(" AND NVL(e2.fullname,e.fullname) = '{0}'", jObject["worker"].ToString());
        }

        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        sql = string.Format(sql, jObject["factoryid"].ToString(), jObject["workcenterid"].ToString(), selectdate[0].ToString(), selectdate[1].ToString());
        return sql;
    }

    // 工人未完成工时
    Result getUnFinishWorktimeChart(JObject jObject)
    {
        string sql = @"
SELECT e.fullname dispatchto,NVL(te.durationperunit,0) durationperunit
FROM employee e
LEFT JOIN sessionvalues sv ON e.employeeid = sv.employeeid
LEFT JOIN workcenter w ON w.workcenterid = sv.workcenterid
LEFT JOIN employeerole er ON er.employeeid = e.employeeid
LEFT JOIN roledef rd ON rd.roleid = er.roleid
LEFT JOIN 
(
   SELECT t.dispatchto,SUM(t.durationperunit) durationperunit 
   FROM 
   (
    {0}
   ) t 
   GROUP BY t.dispatchto
) te ON te.dispatchto = e.fullname
WHERE rd.rolename = '工人' AND sv.workcenterid = '{1}'";
        sql = string.Format(sql, getUnFinishWorktimeSql(jObject), jObject["workcenterid"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //表格数据
    Result getData(JObject jObject)
    {
        int total = 0;
        DataTable dt = getDataCommonMethod(jObject, true, out total);
        Result r = new Result();
        r.data = dt;
        r.total = total;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工区本月--明细
    Result getThisMonthChartDetail(JObject jObject)
    {
        int total = 0;
        DataTable dt = getDataCommonMethod(jObject, true, out total);
        Result r = new Result();
        r.data = dt;
        r.total = total;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //第一级子零件批次
    Result getChildContainer(JObject jObject)
    {
        string sql = getChildContainerSql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工序的时间分布--明细
    Result getContainerPauseDetail(JObject jObject)
    {
        uMESPagingDataDTO dto = dal.GetPagingDataIns(getContainerPauseInfoSql(jObject), (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //个人未完成工时明细
    Result getUnFinishWorktimeDetail(JObject jObject)
    {
        string sql = "SELECT t.* FROM ({0}) t ORDER BY t.productname,t.containername,t.workflowstepname";
        sql = string.Format(sql, getUnFinishWorktimeSql(jObject));
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //第一级子零件批次各工序
    Result getChildContainerSpec(JObject jObject)
    {
        string sql = @"
SELECT cci.containername,cci.qty,ws.workflowstepname,ws.sequence,sd.durationperunit,csd.dispatchemployeename,csd.dispatchdate,
       csd.reportemployeename,csd.reportdate,csd.checkemployeename,csd.checkdate,
       CASE WHEN cm.containercommidetailid IS NOT NULL THEN '已外委'
            WHEN csd.checkdate IS NOT NULL THEN '已完工'
            WHEN csd.reportdate IS NOT NULL THEN '待检验'
            WHEN csd.dispatchdate IS NOT NULL THEN '待报工'
            WHEN csd.dispatchdate IS NULL THEN '待派工' 
       END status,
       CASE WHEN cm.containercommidetailid IS NOT NULL THEN '是' ELSE '否' END isww,csw.checktype
FROM containercurrentinfo cci
LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepname = ws.workflowstepname
LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = csd.containersteprecordid
WHERE s.opdesc <> '虚拟序' AND cci.containerid = '{0}'
ORDER BY ws.sequence";
        sql = string.Format(sql, jObject["containerid"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //缺件
    Result getMissingPart(JObject jObject)
    {
        string sql = @"
SELECT cci2.containername,cci2.qty,cci2.productname,cci2.productdescription,
       CASE WHEN gz.isallset = '否' THEN '否' ELSE '是' END isallset
FROM containercurrentinfo cci
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
LEFT JOIN gzpreparemanagement gz ON gz.containerid = cci2.containerid
WHERE gz.isallset = '否' AND cci.containername = '{0}'";
        sql = string.Format(sql, jObject["containername"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //未完成子零件信息
    Result getNotFinishedChildContainer(JObject jObject)
    {
        string sql = @"
SELECT cci2.containername,cci2.qty,cci2.productname,cci2.productdescription,cci2.isinstore
FROM containercurrentinfo cci
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
LEFT JOIN gzpreparemanagement gz ON gz.containerid = cci2.containerid
WHERE cci.containername = '{0}' AND cci2.isinstore = 0";
        sql = string.Format(sql, jObject["containername"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询跳序信息
    Result getSkipStepInfo(JObject jObject)
    {
        string sql = @"
SELECT t.containername,t.workflowstepname
FROM (
    SELECT cci.containername,cci.qty,
           ws.workflowstepname,csd.dispatchdate,csd.dispatchemployeename,csd.reportdate,csd.reportemployeename
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
    LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND cci.isinstore = 1 AND cm.containercommidetailid IS NULL
          AND csd.checkdate IS NULL AND f.factoryname = '{0}' AND cci.containername = '{1}'

    UNION ALL

    SELECT cci2.containername,cci2.qty,
           ws.workflowstepname,csd.dispatchdate,csd.dispatchemployeename,csd.reportdate,csd.reportemployeename
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN bomproductsubproduct bom ON bom.productname = cci.productname AND bom.topproductrev = cci.productrevision
    LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
    LEFT JOIN factory f ON f.factoryid = cci2.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND cci.isinstore = 1 AND cm.containercommidetailid IS NULL
          AND csd.checkdate IS NULL AND f.factoryname = '{0}' AND cci.containername = '{1}'
) t
GROUP BY t.containername,t.workflowstepname
ORDER BY t.containername,t.workflowstepname
";
        sql = string.Format(sql, jObject["factoryname"].ToString(), jObject["containername"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询子批次跳序信息
    Result getChildContainerSkipInfo(JObject jObject)
    {
        string sql = @"
SELECT t.containername,t.workflowstepname
FROM (
    SELECT cci2.containername,cci2.qty,
           ws.workflowstepname,csd.dispatchdate,csd.dispatchemployeename,csd.reportdate,csd.reportemployeename
    FROM containercurrentinfo cci2
    LEFT JOIN factory f ON f.factoryid = cci2.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE s.opdesc <> '虚拟序' AND cci2.isinstore = 1 AND cm.containercommidetailid IS NULL
          AND csd.checkdate IS NULL AND f.factoryname = '{0}' AND cci2.containername = '{1}'
) t
GROUP BY t.containername,t.workflowstepname
ORDER BY t.containername,t.workflowstepname
";
        sql = string.Format(sql, jObject["factoryname"].ToString(), jObject["containername"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        int total = 0;
        DataTable dtResult = getDataCommonMethod(jObject, false, out total);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("containerid");
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["containername"].Caption = "零件/批次号";
        dtResult.Columns["workcentername"].Caption = "主制工区";
        dtResult.Columns["currentspec"].Caption = "当前工序";
        dtResult.Columns["startdate"].Caption = "开始时间";
        dtResult.Columns["enddate"].Caption = "结束时间";
        dtResult.Columns["ordertype"].Caption = "品种类型";
        dtResult.Columns["plantype"].Caption = "计划类型";
        dtResult.Columns["priority"].Caption = "重要等级";
        dtResult.Columns["risklevel"].Caption = "风险程度";
        dtResult.Columns["delaystatus"].Caption = "是否超期";
        dtResult.Columns["isinstore"].Caption = "是否入库";
        dtResult.Columns["totalworktime"].Caption = "总工时";
        dtResult.Columns["finishedworktime"].Caption = "已完工工时";
        dtResult.Columns["worktimerate"].Caption = "工时完成率";
        dtResult.Columns["finishinfo"].Caption = "子零件完成数";
        dtResult.Columns["isskip"].Caption = "是否存在跳序";
        dtResult.Columns["isallset"].Caption = "是否齐套";
        dtResult.Columns["lackqty"].Caption = "材料缺件情况";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["weekplanfinishdate"].Caption = "周计划完成时间";
        ExportHelper.ExportExcel(dtResult, "工装工具厂工区看板v3.0");
    }

    //查询是否跳序数据
    DataTable getIsSkipData(string strParams)
    {
        string sql = @"
SELECT t.topcontainerid,CASE WHEN SUM(DECODE(t.checkdate,NULL,1,0)) > 0 THEN '是' ELSE '否' END isskip
FROM
(
    SELECT cci.containerid topcontainerid,cci.containername topcontainername,
            cci.containerid,cci.containername,
            DECODE(cci.isinstore,1,'是','否') isinstore,
            ws.workflowstepname,ws.workflowstepid,MAX(csd.checkdate) checkdate
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
    LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND cci.isinstore = 1 AND cm.containercommidetailid IS NULL {0}
    GROUP BY cci.containerid,cci.containername,cci.isinstore,ws.workflowstepname,ws.workflowstepid
            
    UNION ALL
            
    SELECT cci.containerid topcontainerid,cci.containername topcontainername,
            cci2.containerid,cci2.containername,
            DECODE(cci2.isinstore,1,'是','否') isinstore,
            ws.workflowstepname,ws.workflowstepid,
            MAX(csd.checkdate) checkdate
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
    LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND cci.isinstore = 1 AND cm.containercommidetailid IS NULL {0}
    GROUP BY cci.containerid,cci.containername,cci2.containerid,cci2.containername,cci2.isinstore,ws.workflowstepname,ws.workflowstepid
) t
GROUP BY t.topcontainerid
HAVING SUM(DECODE(t.checkdate,NULL,1,0)) > 0";
        sql = string.Format(sql, strParams);
        DataTable dtIsSkip = dal.GetDataTable(sql);
        return dtIsSkip;
    }

    //查询顶层批次状态数据
    DataTable getTopContainerStatus(string strParams, JObject jObject)
    {
        string status = "";
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            status = jObject["status"].ToString();
        }
        string sql = @"
SELECT cci.containerid,
       CASE WHEN cci.isinstore = 1 THEN '完工入库'
            WHEN cm.containercommidetailid IS NOT NULL THEN '已外委'
            WHEN csd.checkdate IS NULL AND csd.reportdate IS NOT NULL THEN '待检验'
            WHEN csd.reportdate IS NULL AND csd.dispatchdate IS NOT NULL THEN '待报工'
            WHEN csd.dispatchdate IS NULL THEN '待派工'
       END status
FROM containercurrentinfo cci
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = cci.workflowstepid
LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = cci.workflowstepid AND cm.isuse = 0
WHERE p.istopgz = 1 {0} ";
        if (status == "完工入库")
        {
            sql += string.Format(" AND cci.isinstore = 1");
        }
        if (status == "已外委")
        {
            sql += string.Format(" AND cm.containercommidetailid IS NOT NULL");
        }
        if (status == "待检验")
        {
            sql += string.Format(" AND cci.isinstore = 0 AND cm.containercommidetailid IS NULL AND csd.checkdate IS NULL AND csd.reportdate IS NOT NULL");
        }
        if (status == "待报工")
        {
            sql += string.Format(" AND cci.isinstore = 0 AND cm.containercommidetailid IS NULL AND csd.reportdate IS NULL AND csd.dispatchdate IS NOT NULL");
        }
        if (status == "待派工")
        {
            sql += string.Format(" AND cci.isinstore = 0 AND cm.containercommidetailid IS NULL AND csd.dispatchdate IS NULL");
        }

        sql += @"
GROUP BY cci.containerid,cci.isinstore,csd.dispatchdate,csd.reportdate,csd.checkdate,cm.containercommidetailid";

        sql = string.Format(sql, strParams);
        DataTable dtTopContainerStatus = dal.GetDataTable(sql);
        return dtTopContainerStatus;
    }

    //主查询sql
    string getWorktimeFinishRateSql(string strParams, JObject jObject, string filter)
    {
        string sql = @"
SELECT t.productname,t.topcontainername containername,t.topcontainerid containerid,
       t.workcentername,t.currentspec,
       t.startdate,t.enddate,t.ordertype,t.plantype,t.priority,t.risklevel,
       t.delaystatus,t.isinstore,
       SUM(t.durationperunit) totalworktime,
       SUM(t.worktime) finishedworktime,
       CASE WHEN SUM(t.durationperunit) = 0 THEN 100
            ELSE ROUND(SUM(t.worktime)/SUM(t.durationperunit),2) * 100 
       END worktimerate,t.weekplanfinishdate
FROM (
     SELECT cci.containerid topcontainerid,cci.containername topcontainername,
           cci.productname,od.startdate,od.enddate,od.ordertype,od.plantype,od.priority,od.risklevel,
           cci.containername,cci.containerid,cci.fromworkcentername workcentername,cci.workflowstepname currentspec,
           CASE WHEN TRUNC(SYSDATE) >= od.enddate THEN TRUNC(SYSDATE) - od.enddate || '天' ELSE '否' END delaystatus,
           DECODE(cci.isinstore,1,'是','否') isinstore,
           ws.workflowstepname,ws.workflowstepid,NVL(sd.durationperunit,0) durationperunit,
           CASE WHEN wt.containersteptimesheetid IS NOT NULL THEN COALESCE(wts.splitduration,wt.totalduration,ROUND(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0)
                ELSE 0
           END worktime,od.weekplanfinishdate
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
    LEFT JOIN containersteptimesheet wt ON wt.containerid = cci.containerid AND wt.workflowstepid = ws.workflowstepid
    LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
    LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = cci.workflowstepid
    LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = wt.containerstepworkreportid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE p.istopgz = 1 AND s.opdesc <> '虚拟序' AND cm.containercommidetailid IS NULL AND NVL(csw.checktype,0) <> 1 {0} {1}

    UNION ALL

    SELECT cci.containerid topcontainerid,cci.containername topcontainername,
           cci.productname,od.startdate,od.enddate,od.ordertype,od.plantype,od.priority,od.risklevel,
           cci2.containername,cci2.containerid,cci.fromworkcentername workcentername,cci.workflowstepname currentspec,
           CASE WHEN TRUNC(SYSDATE) >= od.enddate THEN TRUNC(SYSDATE) - od.enddate || '天' ELSE '否' END delaystatus,
           DECODE(cci.isinstore,1,'是','否') isinstore,
           ws.workflowstepname,ws.workflowstepid,NVL(sd.durationperunit,0) durationperunit,
           CASE WHEN wt.containersteptimesheetid IS NOT NULL THEN COALESCE(wts.splitduration,wt.totalduration,ROUND(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0)
                ELSE 0
           END worktime,od.weekplanfinishdate
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
    LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
    LEFT JOIN containersteptimesheet wt ON wt.containerid = cci2.containerid AND wt.workflowstepid = ws.workflowstepid
    LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
    LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = wt.containerstepworkreportid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE p.istopgz = 1 AND s.opdesc <> '虚拟序' AND cm.containercommidetailid IS NULL AND NVL(csw.checktype,0) <> 1 {0} {1}
) t";
        if (jObject["worktimerate"] != null)
        {
            JArray select = JArray.Parse(jObject["worktimerate"].ToString());
            sql += string.Format(" HAVING (CASE WHEN SUM(t.durationperunit) = 0 THEN 100 ELSE ROUND(SUM(t.worktime)/SUM(t.durationperunit),2) * 100 END) BETWEEN {0} AND {1}",
                select[0].ToString(), select[1].ToString());
        }

        sql += @"
GROUP BY t.productname,t.startdate,t.enddate,t.ordertype,t.plantype,t.topcontainername,t.topcontainerid,t.workcentername,t.currentspec,
         t.delaystatus,t.isinstore,t.priority,t.risklevel,t.weekplanfinishdate";
        sql = string.Format(sql, strParams, filter);
        return sql;
    }

    //查询子零件完成数
    DataTable getFinishInfoData(string strParams)
    {
        string sql = @"
        SELECT t.topcontainerid,SUM(DECODE(t.isinstore,'是',1,0)) || '/' || COUNT(t.topcontainerid) finishInfo
        FROM 
        (
            SELECT cci.containername topcontainername,
                    cci.containerid topcontainerid,
                    cci2.productname,
                    cci2.containername,cci2.containerid,DECODE(cci2.isinstore,1,'是','否') isinstore
            FROM containercurrentinfo cci
            INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
            LEFT JOIN product p ON p.productid = cci.productid
            LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
            LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
            LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
            WHERE 1=1 {0}
        ) t
        GROUP BY t.topcontainerid";
        sql = string.Format(sql, strParams);
        return dal.GetDataTable(sql);
    }

    //查询是否齐套、缺件
    DataTable getLackInfo(string strParams, JObject jObject)
    {
        string isallset = "";
        if (jObject["isallset"] != null && jObject["isallset"].ToString() != "")
        {
            isallset = jObject["isallset"].ToString();
        }
        string sql = @"
        SELECT cci.containerid,
               CASE WHEN SUM(DECODE(CASE WHEN gz.isallset = '否' THEN '否' ELSE '是' END,'否',1,0)) > 0 THEN '否' ELSE '是' END isallset,
               SUM(DECODE(CASE WHEN gz.isallset = '否' THEN '否' ELSE '是' END,'否',1,0)) lackqty
        FROM containercurrentinfo cci
        INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
        LEFT JOIN product p ON p.productid = cci.productid
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
        LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
        LEFT JOIN gzpreparemanagement gz ON gz.containerid = cci2.containerid
        WHERE 1=1 {0}";
        if (isallset == "是")
        {
            sql += " HAVING SUM(DECODE(CASE WHEN gz.isallset = '否' THEN '否' ELSE '是' END,'否',1,0)) = 0 ";
        }
        if (isallset == "否")
        {
            sql += " HAVING SUM(DECODE(CASE WHEN gz.isallset = '否' THEN '否' ELSE '是' END,'否',1,0)) > 0 ";
        }
        sql += @"
        GROUP BY cci.containerid";
        sql = string.Format(sql, strParams);
        return dal.GetDataTable(sql);
    }
}